{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%matplotlib notebook\n",
    "\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "\n",
    "sns.set_context(\"paper\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "* [Intro](#Intro)\n",
    "* [Data Loading](#Data-Loading)\n",
    "* [Function Application](#Function-Application)\n",
    "* [Group-By and Aggregation](#Group-By-and-Aggregation)\n",
    "\t* [Plotting](#Plotting)\n",
    "\t* [Multi-Index](#Multi-Index)\n",
    "* [Pivoting, Stacking and Melting](#Pivoting,-Stacking-and-Melting)\n",
    "\t* [Facet Grid](#Facet-Grid)\n",
    "* [Filling time ranges](#Filling-time-ranges)\n",
    "* [Notes](#Notes)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, I'm going to demonstrate with practical examples various concepts and methods related to Pandas and Seaborn. I will rely on the data format I used for my [Facebook Conversation Analyzer project](https://github.com/5agado/conversation-analyzer). For seemingly obvious reasons I didn't use a personal conversation but automatically generated a fake and nonsensical one. Projecting or imagining some conversation relevant to you will most likely help you to better understand and memorize the content of this notebook, even greater if you can play around with your actual data.\n",
    "\n",
    "The main topic is data manipulation with Pandas, for example function application, groupby, aggregation and multi-indexes. All along I'll mention handy tricks that you can use for various tasks and demonstrate how we can plot results in different ways using [Seaborn](http://seaborn.pydata.org/index.html) (based on matplotlib). Given the data format, special focus is put on time-series data manipulation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Loading"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assuming you already have your data in a valid csv format, loading it in a Pandas dataframe is as easy as calling [*pd.read_csv*](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html).\n",
    "\n",
    "We additionally care about the **automatic datetime parsing** for certain columns. With the *parse_dates* argument, we tell Pandas which date should be forced to datetime format. You can also pass a custom parser, something like: *date_parser = lambda x : pd.datetime.strptime(x, '%H:%M:%S'))*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "format": "row"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>sender</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-11 00:16:51</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I... remember.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-11 19:06:48</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I'm sure you would.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-12 18:10:15</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>Where is my mother! How oddly thou repliest! Y...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-13 21:45:27</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I'm at Space Station Five, darling. How are you?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-14 15:44:06</td>\n",
       "      <td>Frank</td>\n",
       "      <td>You were never invited to my house.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             datetime  sender  \\\n",
       "0 2016-01-11 00:16:51  Donnie   \n",
       "1 2016-01-11 19:06:48  Donnie   \n",
       "2 2016-01-12 18:10:15  Donnie   \n",
       "3 2016-01-13 21:45:27  Donnie   \n",
       "4 2016-01-14 15:44:06   Frank   \n",
       "\n",
       "                                                text  \n",
       "0                                     I... remember.  \n",
       "1                                I'm sure you would.  \n",
       "2  Where is my mother! How oddly thou repliest! Y...  \n",
       "3   I'm at Space Station Five, darling. How are you?  \n",
       "4                You were never invited to my house.  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load conversation messages\n",
    "msgs = pd.read_csv('test_conv.csv', parse_dates=['datetime'])\n",
    "# Display first 5 entries of our dataframe\n",
    "msgs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 3 columns):\n",
      "datetime    1000 non-null datetime64[ns]\n",
      "sender      1000 non-null object\n",
      "text        1000 non-null object\n",
      "dtypes: datetime64[ns](1), object(2)\n",
      "memory usage: 23.5+ KB\n"
     ]
    }
   ],
   "source": [
    "# Double check of types\n",
    "msgs.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Function Application"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Function application allows you to map a function to elements (cells, rows, columns) of your dataframe (or series) in a computationally efficient way.  \n",
    "\n",
    "We will try out function application in the preprocessing step for the textual component of our messages.\n",
    "We are going to use a dummy method for tokenization (transform text to list of words) and then compute some stats on the spot (e.g. text length, word count).\n",
    "\n",
    "When using [*apply*](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.apply.html) on a dataframe, always double check on the *axis* parameter, it's easy to forget or confuse it, causing annoying errors. 0 (default) is for applying the function to each column, 1 to each row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Most simple and dummy method possible for tokenizing your text\n",
    "def get_words(text):\n",
    "    return text.strip().split()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>sender</th>\n",
       "      <th>text</th>\n",
       "      <th>text_len</th>\n",
       "      <th>num_tokens</th>\n",
       "      <th>num_types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-11 00:16:51</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I... remember.</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-11 19:06:48</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I'm sure you would.</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-12 18:10:15</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>Where is my mother! How oddly thou repliest! Y...</td>\n",
       "      <td>100</td>\n",
       "      <td>18</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-13 21:45:27</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I'm at Space Station Five, darling. How are you?</td>\n",
       "      <td>48</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-14 15:44:06</td>\n",
       "      <td>Frank</td>\n",
       "      <td>You were never invited to my house.</td>\n",
       "      <td>35</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             datetime  sender  \\\n",
       "0 2016-01-11 00:16:51  Donnie   \n",
       "1 2016-01-11 19:06:48  Donnie   \n",
       "2 2016-01-12 18:10:15  Donnie   \n",
       "3 2016-01-13 21:45:27  Donnie   \n",
       "4 2016-01-14 15:44:06   Frank   \n",
       "\n",
       "                                                text  text_len  num_tokens  \\\n",
       "0                                     I... remember.        14           2   \n",
       "1                                I'm sure you would.        19           4   \n",
       "2  Where is my mother! How oddly thou repliest! Y...       100          18   \n",
       "3   I'm at Space Station Five, darling. How are you?        48           9   \n",
       "4                You were never invited to my house.        35           7   \n",
       "\n",
       "   num_types  \n",
       "0          2  \n",
       "1          4  \n",
       "2         16  \n",
       "3          9  \n",
       "4          7  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Our function to apply. When applied to a dataframe this will get a row as input\n",
    "def extract_text_basic_stats(row):\n",
    "    # tokenize our message text\n",
    "    words = get_words(row['text'])\n",
    "    # Compute message stats and add entries to the row\n",
    "    # For demonstration purposes, but otherwise clearly inefficient way to do it\n",
    "    row['text_len'] = len(row['text'])\n",
    "    row['num_tokens'] = len(words)\n",
    "    row['num_types'] = len(set(words))\n",
    "    return row\n",
    "\n",
    "# We apply row wise, so axis = 1\n",
    "msgs_stats = msgs.apply(extract_text_basic_stats, axis=1)\n",
    "msgs_stats.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With previous approach (i.e. applying function on dataframe) we can rely on the entire information present in a row. For this specific case is a bit of an overhead, mostly cause we are using only the *text* field. \n",
    "We can then instead simply **apply our function directly to our target column**, and pack the result values in a new dataframe that will be joined with our original one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>sender</th>\n",
       "      <th>text</th>\n",
       "      <th>text_len</th>\n",
       "      <th>num_tokens</th>\n",
       "      <th>num_types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-11 00:16:51</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I... remember.</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-11 19:06:48</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I'm sure you would.</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-12 18:10:15</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>Where is my mother! How oddly thou repliest! Y...</td>\n",
       "      <td>100</td>\n",
       "      <td>18</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-13 21:45:27</td>\n",
       "      <td>Donnie</td>\n",
       "      <td>I'm at Space Station Five, darling. How are you?</td>\n",
       "      <td>48</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-14 15:44:06</td>\n",
       "      <td>Frank</td>\n",
       "      <td>You were never invited to my house.</td>\n",
       "      <td>35</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             datetime  sender  \\\n",
       "0 2016-01-11 00:16:51  Donnie   \n",
       "1 2016-01-11 19:06:48  Donnie   \n",
       "2 2016-01-12 18:10:15  Donnie   \n",
       "3 2016-01-13 21:45:27  Donnie   \n",
       "4 2016-01-14 15:44:06   Frank   \n",
       "\n",
       "                                                text  text_len  num_tokens  \\\n",
       "0                                     I... remember.        14           2   \n",
       "1                                I'm sure you would.        19           4   \n",
       "2  Where is my mother! How oddly thou repliest! Y...       100          18   \n",
       "3   I'm at Space Station Five, darling. How are you?        48           9   \n",
       "4                You were never invited to my house.        35           7   \n",
       "\n",
       "   num_types  \n",
       "0          2  \n",
       "1          4  \n",
       "2         16  \n",
       "3          9  \n",
       "4          7  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Our function to apply. When applied to a series/column this will get a cell value as input\n",
    "def extract_text_basic_stats(text):\n",
    "    words = get_words(text)\n",
    "    # Return results as tuples. You can also return a dictionary for automatic insertion in a dataframe\n",
    "    return len(text), len(words), len(set(words))\n",
    "\n",
    "# Apply function to text column\n",
    "stats = msgs['text'].apply(extract_text_basic_stats).values\n",
    "# Pack results in a new dataframe and join it with our original one\n",
    "msgs_stats = pd.DataFrame(list(stats), columns=['text_len', 'num_tokens', 'num_types'])\n",
    "msgs_stats = msgs.join(msgs_stats)\n",
    "msgs_stats.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Group-By and Aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Stats by day can be too granular for our interests, while seeing the trend per months can provide a better and clearer understanding on what's going on. Seems the right time to rely on [group-by and aggregation](http://pandas.pydata.org/pandas-docs/stable/groupby.html).\n",
    "\n",
    "*groupby* groups your entries by common values, where the values come from field (or set of) you specify. For example, I might group my messages by sender and month, and for each combination of them will obtain a list of associated and recorded values. **Aggregation** is about telling what do do with such list: sum, average, min, max, standard deviation are just some common examples. In the case of more complex types like list or string, you might instead perform concatenation or set operations.\n",
    "\n",
    "When dealing with time-series data is good to consider also the [resample](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.resample.html) method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "format": "row"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>text_len</th>\n",
       "      <th>num_tokens</th>\n",
       "      <th>num_types</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sender</th>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Donnie</th>\n",
       "      <th>1</th>\n",
       "      <td>1311</td>\n",
       "      <td>251</td>\n",
       "      <td>239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1487</td>\n",
       "      <td>280</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2236</td>\n",
       "      <td>436</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2379</td>\n",
       "      <td>449</td>\n",
       "      <td>430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1369</td>\n",
       "      <td>260</td>\n",
       "      <td>248</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              text_len  num_tokens  num_types\n",
       "sender month                                 \n",
       "Donnie 1          1311         251        239\n",
       "       2          1487         280        265\n",
       "       3          2236         436        417\n",
       "       4          2379         449        430\n",
       "       5          1369         260        248"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new feature by extracting the month from the date\n",
    "msgs_stats['month'] = msgs_stats['datetime'].dt.month\n",
    "# Group by month and sender and aggregate by sum\n",
    "grouped_msgs = msgs_stats.groupby(['sender','month']).sum()\n",
    "grouped_msgs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*.dt* is a quick and nice way to access just the part of your datetime object you interested in, something like *.dt.month*, *.dt.year*, *.dt.hour*. Notice you could directly group by month by passing *msgs_stats['datetime'].dt.month* to the groupby list, thus avoiding the need of creating a new month column beforehand.\n",
    "\n",
    "While the previous is a simple example of aggregation, we can also get further and **derive several stats in one go**, by specifying which function to apply for each new column. Again, you could also rely on lambda or previously defines functions, and pass them to the *agg* method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>avg_tokens</th>\n",
       "      <th>num_types</th>\n",
       "      <th>max_tokens</th>\n",
       "      <th>num_tokens</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sender</th>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Donnie</th>\n",
       "      <th>1</th>\n",
       "      <td>7.606061</td>\n",
       "      <td>239</td>\n",
       "      <td>20</td>\n",
       "      <td>251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8.750000</td>\n",
       "      <td>265</td>\n",
       "      <td>20</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9.478261</td>\n",
       "      <td>417</td>\n",
       "      <td>24</td>\n",
       "      <td>436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.803922</td>\n",
       "      <td>430</td>\n",
       "      <td>20</td>\n",
       "      <td>449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7.222222</td>\n",
       "      <td>248</td>\n",
       "      <td>19</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              avg_tokens  num_types  max_tokens  num_tokens\n",
       "sender month                                               \n",
       "Donnie 1        7.606061        239          20         251\n",
       "       2        8.750000        265          20         280\n",
       "       3        9.478261        417          24         436\n",
       "       4        8.803922        430          20         449\n",
       "       5        7.222222        248          19         260"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create destination column for new stats\n",
    "msgs_stats['max_tokens'] = msgs_stats['num_tokens']\n",
    "msgs_stats['avg_tokens'] = msgs_stats['num_tokens']\n",
    "# Groupby and apply specific aggregation to each column we are interested in\n",
    "grouped_msgs = msgs_stats.groupby(['sender','month']).agg({'max_tokens' : np.max, 'avg_tokens' : np.mean,\n",
    "                                                          'num_tokens': sum, 'num_types' : sum})\n",
    "grouped_msgs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Plotting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look into some basic plotting with the current data. A pointplot seems a reasonable representation, and we just have to pass our data to the corresponding method. We can pass directly the x and y values, or pass the entire dataframe as *data* and specify column names associated to x and y. The *hue* parameter allows to specify the column which values will be plotted separately, with different colors. As an example see the representation of sender-specific stats in the following plot.\n",
    "\n",
    "Notice that I often call *reset_index()* before passing the data to the plot function, cause in such a way I can access also index columns by names. I'm currently still not aware of any way to use index names directly.\n",
    "\n",
    "Is good to also consider **alternative options for plotting**, like calling the *plot* function directly provided by Pandas. Or accessing Matplotlibs plot methods via *sns.plt.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<img src=\"\">"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sns.pointplot(x='month', y='num_tokens', data=grouped_msgs.reset_index(), hue='sender')\n",
    "sns.plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multi-Index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When using *groupby* the *as_index* parameter specifies if the columns used for grouping should be used as indexes of the resulting dataframe, if set to *False* such columns will otherwise end up to be part of the other set of columns. So if you group by multiple values, and keep them as index, you will end up with a multi-index dataframe, like the one in our previous example (sender and month).   \n",
    "Understanding and being able to manage [multi-indexes](http://pandas.pydata.org/pandas-docs/stable/advanced.html) is a fundamental requirement when working with more complex dataset, so I will now quickly play with them for a multiplot scenario.\n",
    "\n",
    "Now that you have your resampled data, what do you want to plot?\n",
    "We can reuse this dataframe for different possible plots/queries. We might be interested in the statistics for a specific sender, specific month, combination of the two, or just the overall values.\n",
    "\n",
    "With single index we can access my sender with *.loc['Donnie']*, now with a multi-index we use the *.xs* method instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<img src=\"\">"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create figure with two plots\n",
    "fig, axes = sns.plt.subplots(2, 2, sharey=True)\n",
    "\n",
    "# Get messages for a specific sender and plot values\n",
    "msgs_sender = grouped_msgs.xs('Donnie', level='sender')\n",
    "axes[0,0].set_title('Word Count - Donnie')\n",
    "sns.barplot(x='month', y='num_tokens', data=msgs_sender.reset_index(), ax=axes[0,0], color=\"steelblue\")\n",
    "\n",
    "#or we can plot the total, reusing  groupby, on the index this time, and summing the results\n",
    "msgs_total = grouped_msgs.groupby(level='month').sum()\n",
    "axes[0,1].set_title('Word Count - Total')\n",
    "sns.barplot(x='month', y='num_tokens', data=msgs_total.reset_index(), ax=axes[0,1], color=\"steelblue\")\n",
    "\n",
    "#or check for a single month\n",
    "msgs_month = grouped_msgs.xs(3, level='month')\n",
    "axes[1,0].set_title('Word Count - March')\n",
    "sns.barplot(x='sender', y='num_tokens', data=msgs_month.reset_index(), ax=axes[1,0])\n",
    "\n",
    "#or finally plot all in one, withouth additional selection\n",
    "axes[1,1].set_title('Word Count')\n",
    "sns.barplot(x='month', y='num_tokens', hue='sender', data=grouped_msgs.reset_index(), ax=axes[1,1])\n",
    "\n",
    "sns.plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivoting, Stacking and Melting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is all this about? It's about swirling your dataframe data around in an ordered way, for friends: [reshaping](http://pandas.pydata.org/pandas-docs/stable/reshaping.html). \n",
    "\n",
    "With **pivot** you directly control which columns end up to form your index, which your columns, and which your values.\n",
    "\n",
    "**Stacking** operates a pivoting of columns (different columns become a single new attribute, duplicating the number of samples for each column label).  \n",
    "In the following lines we stack our messages statistics into a single *stats* column, that specifies which statistics we are considering, and has the associated value in the *val* column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>num_tokens</th>\n",
       "      <th>num_types</th>\n",
       "      <th>max_tokens</th>\n",
       "      <th>avg_tokens</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sender</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Donnie</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>2016</th>\n",
       "      <td>124</td>\n",
       "      <td>117</td>\n",
       "      <td>124</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>127</td>\n",
       "      <td>122</td>\n",
       "      <td>127</td>\n",
       "      <td>127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>2016</th>\n",
       "      <td>150</td>\n",
       "      <td>140</td>\n",
       "      <td>150</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>130</td>\n",
       "      <td>125</td>\n",
       "      <td>130</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>2016</th>\n",
       "      <td>196</td>\n",
       "      <td>187</td>\n",
       "      <td>196</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   num_tokens  num_types  max_tokens  avg_tokens\n",
       "sender month year                                               \n",
       "Donnie 1     2016         124        117         124         124\n",
       "             2017         127        122         127         127\n",
       "       2     2016         150        140         150         150\n",
       "             2017         130        125         130         130\n",
       "       3     2016         196        187         196         196"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First prepare data by extracting year info and group-by sum\n",
    "msgs_stats['year'] = msgs_stats['datetime'].dt.year\n",
    "yearly_msgs = msgs_stats.groupby(['sender','month','year']).sum()\n",
    "yearly_msgs = yearly_msgs.drop('text_len', axis=1)\n",
    "yearly_msgs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sender</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>stat</th>\n",
       "      <th>val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Donnie</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>num_tokens</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Donnie</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>num_types</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Donnie</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>max_tokens</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Donnie</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>avg_tokens</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Donnie</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>num_tokens</td>\n",
       "      <td>127</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sender  month  year        stat  val\n",
       "0  Donnie      1  2016  num_tokens  124\n",
       "1  Donnie      1  2016   num_types  117\n",
       "2  Donnie      1  2016  max_tokens  124\n",
       "3  Donnie      1  2016  avg_tokens  124\n",
       "4  Donnie      1  2017  num_tokens  127"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Stack our data and rename columns\n",
    "yearly_msgs = yearly_msgs.stack().reset_index()\n",
    "yearly_msgs.columns.values[3] = 'stat'\n",
    "yearly_msgs.columns.values[4] = 'val'\n",
    "yearly_msgs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Facet Grid"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This format doesn't follow the [tidy data guidelines](http://vita.had.co.nz/papers/tidy-data.pdf), but can be useful for quick playing around and plotting, especially when using Seaborn [FacetGrid](http://seaborn.pydata.org/generated/seaborn.FacetGrid.html). We compose our plot specifying how subplots are built in terms of rows and columns, for then applying to each a basic plotting function.\n",
    "\n",
    "In the following example each column is a year, and different rows are different statistics about our messages. Each plot will then show the values along months, for each sender. Even if this explanation sounds convoluted, the actual plot is really intuitive and helpful when visualizing such kind of data. \n",
    "\n",
    "The **sharing of axis** (keep the same scale for the plots) is also an important part of the grid, that can help visualize how your values vary among different plots. At the same time some sharing might just be counterproductive, for example when comparing values with a big scale or range difference. We have one of such cases when comparing *text_len* with the other stats."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<img src=\"\">"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create grid specifying to use years for columns and stats for rows\n",
    "grid = sns.FacetGrid(yearly_msgs, col='year', row='stat', margin_titles=True, sharey=True, legend_out=True)\n",
    "# Map to each grid sub-plot a barplot\n",
    "grid.map(sns.barplot, 'month', 'val', 'sender')\n",
    "grid.axes[0][0].legend()\n",
    "sns.plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filling time ranges"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you look closely at our messages occurrences, you can see that not all days are covered. If you index by date you will end up with something like the dataframe showed next.\n",
    "\n",
    "During the analysis part as operated until now this content might be okay and exactly what we need, but when plotting, missing entries are just misleading, showing a consecutive change for non-consecutive dates. What we might do is **fill our time index with all possible dates**, and associate with the new entry the most appropriate value. \n",
    "Missing-value filling deserves an article itself; however, for our dataset, the most appropriate option is to simply assign to each nonpresent entry a value of 0, correctly representing that for such a day no messages have been sent.\n",
    "\n",
    "To build our complete list of dates we can rely on Pandas *date_range* method, then [reindex](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html) the dataframe and insert our new values. In the final plot, you will notice that all dates are now present and with associated value of 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>text_len</th>\n",
       "      <th>num_tokens</th>\n",
       "      <th>num_types</th>\n",
       "      <th>max_tokens</th>\n",
       "      <th>avg_tokens</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-23</th>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-25</th>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-26</th>\n",
       "      <td>67</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-27</th>\n",
       "      <td>44</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-29</th>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-02-02</th>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-02-04</th>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-02-06</th>\n",
       "      <td>56</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-02-07</th>\n",
       "      <td>95</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-02-08</th>\n",
       "      <td>45</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            text_len  num_tokens  num_types  max_tokens  avg_tokens\n",
       "date                                                               \n",
       "2016-01-23        12           2          2           2           2\n",
       "2016-01-25        21           4          4           4           4\n",
       "2016-01-26        67          13         11          13          13\n",
       "2016-01-27        44          10         10          10          10\n",
       "2016-01-29        17           4          4           4           4\n",
       "2016-02-02        22           4          4           4           4\n",
       "2016-02-04        28           6          6           6           6\n",
       "2016-02-06        56           8          8           8           8\n",
       "2016-02-07        95          19         19          19          19\n",
       "2016-02-08        45           8          8           8           8"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop unused columns\n",
    "msgs_stats.drop(['year', 'month'], axis=1, inplace=True)\n",
    "# Extract date as new column\n",
    "msgs_stats['date'] = msgs_stats['datetime'].dt.date\n",
    "# Group-by date and sum, selecting just a subset of samples\n",
    "fill_time_msgs = msgs_stats.groupby(['date']).sum().iloc[:20,:]\n",
    "# Show example of discontinuous entries\n",
    "fill_time_msgs[10:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[datetime.date(2016, 1, 11), datetime.date(2016, 1, 12), datetime.date(2016, 1, 13), datetime.date(2016, 1, 14), datetime.date(2016, 1, 15), datetime.date(2016, 1, 16), datetime.date(2016, 1, 17), datetime.date(2016, 1, 18), datetime.date(2016, 1, 19), datetime.date(2016, 1, 20), datetime.date(2016, 1, 21), datetime.date(2016, 1, 22), datetime.date(2016, 1, 23), datetime.date(2016, 1, 24), datetime.date(2016, 1, 25), datetime.date(2016, 1, 26), datetime.date(2016, 1, 27), datetime.date(2016, 1, 28), datetime.date(2016, 1, 29), datetime.date(2016, 1, 30), datetime.date(2016, 1, 31), datetime.date(2016, 2, 1), datetime.date(2016, 2, 2), datetime.date(2016, 2, 3), datetime.date(2016, 2, 4), datetime.date(2016, 2, 5), datetime.date(2016, 2, 6), datetime.date(2016, 2, 7), datetime.date(2016, 2, 8)]\n"
     ]
    }
   ],
   "source": [
    "# Generate our dates range using dates of first and last message\n",
    "start_date = fill_time_msgs.index.values[0]\n",
    "end_date = fill_time_msgs.index.values[-1]\n",
    "dates = pd.date_range(start=start_date, end=end_date)\n",
    "dates = [d.date() for d in dates]\n",
    "print(dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<img src=\"\">"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reindex using dates, specify as filling value 0, as previously discussed\n",
    "fill_time_msgs = fill_time_msgs.reindex(dates, fill_value=0)\n",
    "fill_time_msgs.index.name = 'date'\n",
    "# Plot data\n",
    "sns.pointplot(x='date', y='text_len', data=fill_time_msgs.reset_index())\n",
    "sns.plt.gcf().autofmt_xdate()\n",
    "sns.plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'm using this notebook as a personal \"wiki\" and practice field, as a mean to clarify my understanding and knowledge of the topic. I chose to share cause you know, you tend to do better when you go public, but also I'm hoping for possible feedback, comments or critiques. In the future I might explore other important operations here missing (take joining and merging for example), or go into more specific details on things already explored, also based on eventual feedback.\n",
    "\n",
    "**Pandas and Seaborn are pretty cool**, both for personal or work related projects, but I'm also open to possible new alternatives or additions to use for the task of data analysis and visualization, so feel free to give me your feedback on this too."
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda env:kaggle]",
   "language": "python",
   "name": "conda-env-kaggle-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3.0
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}